﻿CREATE TABLE [dbo].[SiteObjects](
	[FarmID] [uniqueidentifier] NOT NULL,
	[WebApplicationID] [uniqueidentifier] NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[ObjectID] [nvarchar](512) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[ObjectType] [tinyint] NOT NULL,
	[Url] [nvarchar](255) NULL,
	[Index] [bit] NOT NULL,
	[IndexScope] [tinyint] NOT NULL,
	[IndexByProperty] [tinyint] NOT NULL,
	[IndexItems] bit,
	[IndexID] [nvarchar](512) NOT NULL,
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[SiteObjects]  WITH NOCHECK ADD  CONSTRAINT [FK_SiteObjects_SiteInfo] FOREIGN KEY([SiteID], [WebID])
REFERENCES [dbo].[SiteInfo] ([SiteID], [WebID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[SiteObjects] CHECK CONSTRAINT [FK_SiteObjects_SiteInfo]
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetSiteObjects]
	@FarmID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@ObjectType tinyint
AS
BEGIN
	select
		FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId 
	from
		SiteObjects 
	where
		FarmID=@FarmID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetIndexObjects]
	@FarmID UniqueIdentifier
AS
BEGIN
	select
		FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId 
	from
		SiteObjects 
	where
		FarmID=@FarmID and [Index]=1
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[UpdateSiteObjects]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@ObjectType tinyint,
	@ObjectID nvarchar(512),
	@Name nvarchar(255),
	@Url nvarchar(255),
	@Index bit,
	@IndexScope tinyint,
	@IndexByProperty tinyint,
	@IndexItems bit,
	@IndexId nvarchar(512)
AS
BEGIN
if exists (select ObjectID from SiteObjects where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType and ObjectID=@ObjectID)
begin
	update
		SiteObjects 
	set 
		Name = @Name, Url=@Url, [Index]=@Index, IndexScope=@IndexScope, IndexByProperty=@IndexByProperty, IndexItems=@IndexItems, IndexId=@IndexId
	where 
		FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType and ObjectID=@ObjectID
end else
begin
	insert into SiteObjects
		(FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId)
	values
		(@FarmID, @WebApplicationID, @SiteID, @WebID, @ObjectID, @Name, @ObjectType, @Url, @Index, @IndexScope, @IndexByProperty, @IndexItems, @IndexId)
end

END
GO